package com.blog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import com.blog.dto.Tag;
import com.blog.util.DataAccess;
import com.blog.util.DateTime;

public class TagDao {
	
	/**
	 * 用户根据自己的用户id添加标签
	 * @param user_id
	 * @param _tag
	 * @return
	 */
	public boolean addTagByUserIdAndTagName( int user_id, String _tagName ) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into tag values(null,?,?,null)";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _tagName );
			ps.setInt(2, user_id);
			
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	public boolean addTag( Tag _tag ) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into tag values(null,?,?,null)";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _tag.getTag_name() );
			ps.setInt(2, _tag.getUser_id());
			
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 用户根据自己的用户名添加标签
	 * @param _usename
	 * @param _tag
	 * @return
	 */
	public boolean addTagByuserame( String _username, String _tag) {
		boolean flag = false ;
		UserDao userDao = new UserDao();
		int user_id = userDao.findUserIdByName(_username);
		addTagByUserIdAndTagName(user_id, _tag);
		return flag;
	}
	
	/**
	 * 根据用户id和对应的标签名删除标签
	 * @param user_id
	 * @param _tag
	 * @return
	 */
	public boolean delTagByUserIdAndtagName( int user_id , String _tag) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  tag set  delete_at = ?  " + 
				"  where user_id = ? and tag_name = ? and (delete_at is null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setInt(2, user_id);
			ps.setString(3, _tag);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据用户名和对应的标签名删除标签
	 * @param _usename
	 * @param _tag
	 * @return
	 */
	
	public boolean delTagByTag_id( int tag_id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  tag set  delete_at = ?  " + 
				"  where tag_id = ? and (delete_at is null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setInt(2, tag_id);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 删除标签
	 * @param _tag
	 * @return
	 */
	public boolean delTag( Tag _tag) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  tag set  delete_at = ?  " + 
				"  where user_id = ? and tag_name = ? and (delete_at is null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setInt(2, _tag.getUser_id());
			ps.setString(3, _tag.getTag_name());
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 修改标签
	 * @param _usename
	 * @param _tag
	 * @return
	 */
	public boolean editTag( Tag _tag) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  tag set  user_id = ? , tag_name = ? " + 
				"  where tag_id = ? and (delete_at is null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _tag.getUser_id());
			ps.setString(2, _tag.getTag_name());
			ps.setInt(3, _tag.getTag_id());
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	
	/**
	 * 查找所有标签
	 * @return
	 */
	public Vector<Tag> findAllTag() {
		Vector<Tag> v=new Vector<Tag>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps=null;
		ResultSet rs = null ;
		String sql= "select * from tag where delete_at is NULL ";
		try {
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				Tag _t = new Tag();
				_t.setTag_id(rs.getInt("tag_id"));
				_t.setTag_name(rs.getString("tag_name"));
				_t.setUser_id(rs.getInt("user_id"));
				v.add(_t);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 查找用户定义的所有标签，返回多个标签
	 * @param _id
	 * @return
	 */
	public Vector<Tag> findTagByUser_id(int _id) {
		Vector<Tag> v=new Vector<Tag>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps=null;
		ResultSet rs = null ;
		String sql= "select * from tag where user_id = ? AND (delete_at is NULL) ";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, _id);
			rs=ps.executeQuery();
			while(rs.next()) {
				Tag _t = new Tag();
				_t.setTag_id(rs.getInt("tag_id"));
				_t.setTag_name(rs.getString("tag_name"));
				_t.setUser_id(rs.getInt("user_id"));
				v.add(_t);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据标签的ID查找，返回标签
	 * @param _id
	 * @return
	 */
	public Vector<Tag> findTagByTag_id(int _id) {
		Vector<Tag> v=new Vector<Tag>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps=null;
		ResultSet rs = null ;
		String sql= "select * from tag where tag_id = ? AND (delete_at is NULL) ";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, _id);
			rs=ps.executeQuery();
			while(rs.next()) {
				Tag _t = new Tag();
				_t.setTag_id(rs.getInt("tag_id"));
				_t.setTag_name(rs.getString("tag_name"));
				_t.setUser_id(rs.getInt("user_id"));
				v.add(_t);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据用户id和tag名查找标签，返回是否存在
	 * @param _id
	 * @param _tag
	 * @return
	 */
	public boolean findTagByUser_idAndTag(int _id , String _tag) {
		boolean flag = false;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "select * from tag where user_id = ? AND tag_name = ? AND delete_at is NULL ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _id);
			ps.setString(2, _tag);
			if(ps.executeQuery().next()) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据用户id和tag名查找标签，返回标签Id
	 * @param _id
	 * @param _tag
	 * @return
	 */
	public int  findTagByUser_idAndTag( String _tag ,int _id ) {
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		int tag_id = 0;
		String sql = "select * from tag where user_id = ? AND tag_name = ? AND delete_at is NULL ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _id);
			ps.setString(2, _tag);
			rs = ps.executeQuery();
			if(rs.next()) tag_id =rs.getInt("tag_id") ;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return tag_id;
	}
	
	/**
	 * 根据用户id和tag名查找标签，返回标签Id
	 * @param _id
	 * @param _tag
	 * @return
	 */
	public Vector<Tag> findTagByUserIdAndTag( int _id , String _tag ) {
		Vector<Tag> v=new Vector<Tag>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		String sql = "select * from tag where user_id = ? AND tag_name = ? AND delete_at is NULL ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _id);
			ps.setString(2, _tag);
			rs = ps.executeQuery();
			while(rs.next()) {
				Tag _t = new Tag();
				_t.setTag_id(rs.getInt("tag_id"));
				_t.setTag_name(rs.getString("tag_name"));
				_t.setUser_id(rs.getInt("user_id"));
				v.add(_t);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return v;
	}
	

	
	
	
}
